<?php
/**
 * 数据基类,在框架基础上进行了2次封装,适应oracle数据库
 * @author Administrator
 *
 */
class CDBModelBase extends CDBModel {
	
	function __construct()
	{
		
	}
	
	public function getTabelName(){
		return "";
	}
	
	public function getPKID(){
		return "";
	}
	
	protected function getSequence()
	{
		return "";
	}
		
	protected $_dateColumns="";
	
	protected $_moneyColumns = array();
	
	protected $_longColumns = "";
	
	public function getSysDate()
	{
		return date("Y-m-d H:i:s");
	}
	
	/**
	 * 获取日期字段
	 * @return array
	 */
	protected function getDateColumns()
	{
		$colArr = array("create_time","update_time");
		if($this->_dateColumns)
		{
			$colStrArr = explode(",", $this->_dateColumns);
			
			$colArr = array_unique(array_merge($colArr,$colStrArr));
		}
		return $colArr;
	}

	/**
	 * 组合参数
	 * @param array $param
	 */
	protected function getParams($param)
	{
		$r = array();
		if(count($param)>0)
		{
			//$dateCol = $this->getDateColumns();
			foreach($param as $key=>$item)
			{
				$r[] = array(":".$key,$item,PDO::PARAM_STR);
			}
		}
		
		return $r;
	}

	/**
	 * 执行新增操作，并根据条件返回新增的主键ID
	 * @param array $param 参数
	 * @param bool $getPkId 是否需要返回新增的主键ID
	 * @param string $table 操作的表，如果为空则取getTabelName()
	 * @param bool $autoPkId 新增时是否自增列，为true时，需要设置Sequence值
	 * @return 如果设置$getPkId为true，则返回新增的主键，否则不返回
	 */
	public function insert($param,$getPkId=true,$table="",$autoPkId=true)
	{
		$sqlAndParam = $this->getSql("insert", $param,$table,$autoPkId, $getPkId);
		
		if(array_key_exists($this->_longColumns,$param))
		{
			$this->db->insertLongData($sqlAndParam["sql"],$param,$this->_longColumns);
		}
		else{
			$this->db->executeNonQuery($sqlAndParam["sql"],$sqlAndParam["param"]);
		}
		if($getPkId && $autoPkId)
		{
			return $sqlAndParam["pkID"];
		}
	}
	
	/**
	 * 根据主键ID保存记录
	 * @param array $param 参数
	 * @param string $table 操作的表，如果为空则取getTabelName()
	 */
	public function updateByPkId($param,$table="")
	{
		$sqlAndParam = $this->getSql("update", $param,$table);
		$r = true;
		if(count($sqlAndParam["param"])>1)
		{
			$r = $this->db->executeNonQuery($sqlAndParam["sql"],$sqlAndParam["param"]);
		}
		$f = true;
		if(array_key_exists($this->_longColumns,$param))
		{
			if(!$table)
			{
				$table = $this->getTabelName();
			}
			$pkID = $this->getPKID();
			$f = $this->db->updateLongData($table,$pkID,$param[$pkID],$this->_longColumns,$param[$this->_longColumns]);
		}
		
		return $r&&$f;
	}
	
	/**
	 * 根据主键逻辑删除
	 * @param array $param 参数
	 * @param string $table 操作的表，如果为空则取getTabelName()
	 */
	public function deleteByPkId($pkIdValue,$table="")
	{
		$param = array(array(":".$this->getPKID(), $pkIdValue,PDO::PARAM_INT));
		
		if(!$table)
		{
			$table = $this->getTabelName();
		}
		
		return $this->db->executeNonQuery("update ".$table." set DEL_FLAG=1 where ".$this->getPKID()."=:".$this->getPKID(),$param);
	}
	
	/**
	 * 根据参数组合sql语句
	 * @param string $op 操作动作，支持insert,update
	 * @param array $param 参数 array("key"=>value)
	 * @param string $table 操作的表，如果为空则取getTabelName()
	 * @param bool $autoPkId 只在新增时有效,新增时是否自增列，为true时，需要设置Sequence值
	 * @param bool $getPkId 只在新增时有效,是否需要返回新增的主键ID
	 * @return 
	 */
	protected function getSql($op,$param,$table="",$autoPkId=true,$getPkId=true)
	{
		$sql = "";
	
		if(!$table)
		{
			$table = $this->getTabelName();
		}
		
		if(!$table)
		{
			CError::handler(E_ERROR,"表不能为空",__FILE__,__LINE__);
			return;
		}
		
		$r = array();
		$column = "";
		$columnValue = "";
		
		$pkID = 0;
		
		if(count($param)>0)
		{
			$dateCol = $this->getDateColumns();
			foreach($param as $key=>$item)
			{
				if("insert"==$op)
				{
					if($this->_longColumns && array_key_exists($this->_longColumns,$param))
					{
						$r[$key] = $item;
					}
					else
					{
						$r[] = array(":".$key,$item,PDO::PARAM_STR);
					}
					$column .=$key.",";
					if($key==$this->_longColumns)
					{
						$columnValue .= "EMPTY_CLOB(),";
					}
					else{
						if(in_array($key, $dateCol))
						{
							$columnValue .= "to_date(:".$key.",'yyyy/mm/dd hh24:mi:ss'),";
						}
						else
						{
							$columnValue .= ":".$key.",";
						}
					}
				}
				else if("update"==$op)
				{
					if($key==$this->_longColumns)
					{
						continue;
					}
					
					$r[] = array(":".$key,$item,PDO::PARAM_STR);
					if($key==$this->getPKID())
					{
						continue;
					}
					
					if(in_array($key, $dateCol))
					{
						$column .= $key."=to_date(:".$key.",'yyyy/mm/dd hh24:mi:ss'),";
					}
					else
					{
						$column .= $key."=:".$key.",";
					}
				}
			}
			
			$column = substr($column, 0,strlen($column)-1);
			if($columnValue)
			{
				$columnValue = substr($columnValue, 0,strlen($columnValue)-1);
			}
		}

		
		switch($op)
		{
			case "insert":
				if($autoPkId)
				{
					if(!$this->getSequence())
					{
						CError::handler(E_ERROR,"没有设置对应的序列",__FILE__,__LINE__);
						return;
					}
					
					if($getPkId)
					{
						$pkID=$this->getSequenceNextVal();
						
						$sql = "insert into ".$table."(".$this->getPKID().",".$column.") values (".$pkID.",".$columnValue.")";
					}
					else
					{
						$sql = "insert into ".$table."(".$this->getPKID().",".$column.") values (".$this->getSequence().".NEXTVAL,".$columnValue.")";
					}
				}
				else
				{
					$sql = "insert into ".$table."(".$column.") values (".$columnValue.")";
				}
				break;
			case "update":
				$sql = "update ".$table." set ".$column." where ".$this->getPKID()."=:".$this->getPKID();
				break;
		}
		
		if($pkID)
		{
			return array("sql"=>$sql,"param"=>$r, "pkID"=>$pkID);
		}
		
		return array("sql"=>$sql,"param"=>$r);
	}
	
	
	protected function getSequenceNextVal()
	{
		$sql = "select ".$this->getSequence().".NEXTVAL from dual";

		return $this->db->executeScalar($sql);
	}
	
	private function opDataSelectField($field)
	{
		if($field=="*")
		{
			return $field;
		}
		$dateCol = $this->getDateColumns();
		
		$temp = explode(",",$field);
		
		$field = "";
		foreach($temp as $v)
		{
			$v = trim($v);
			
			if(in_array($v, $dateCol))
			{
				$field .= "to_char(".$v.",'yyyy-mm-dd hh24:mi:ss') as ".$v.",";
			}
			else if(in_array($v,$this->_moneyColumns))
			{
				$field .= "decode(substr(".$v.",1,1),'.','0'||".$v.",".$v.") as ".$v.",";
			}
			else{
				$field .= $v.",";
			}
		}
		
		return substr($field,0,strlen($field)-1);
	}
	
	private function selectByWhereExec($execFun, $table="",$field="*",$where=array(),$whereStr = "",$order="",$group="")
	{
		$param = array();
		
		if($table=="")
		{
			$table = $this->getTabelName();
		}
		
		$field = $this->opDataSelectField($field);
		
		$sql = "select ".$field." from ".$table;
		
		$sqlwhere = "";
		
		if($whereStr)
		{
			$sqlwhere = " where ".$whereStr;
		}
		
		foreach($where as $key=>$item)
		{
			if($sqlwhere == "")
			{
				$sqlwhere = " where ";
			}
			else{
				$sqlwhere .= " and ";
			}
				
				
			if(is_string($item))
			{
				$param[] = array(":".$key,$item,PDO::PARAM_STR);
				$sqlwhere .= $key."=:".$key;
			}
			else if(is_array($item)){
				if(!isset($item["v"]))
				{
					CError::handler(E_ERROR,"需要绑定的数据参数不正确",__FILE__,__LINE__);
					return false;
				}
				$param[] = array(":".$key,$item["v"],PDO::PARAM_STR);
				
				$operateor="=";
				if(isset($item["operateor"]))
				{
					$operateor = $item["operateor"];
				}
				if(isset($item["table"]))
				{
					$sqlwhere .= $item["table"].".".$key.$operateor.":".$key;
				}
				else{
					$sqlwhere .= $key.$operateor.":".$key;
				}
			}
		}
		$sql = $sql.$sqlwhere;
		if($order)
		{
			$sql .= " order by ".$order;
		}
		
		if($group)
		{
			$sql .= " group by ".$group;
		}

		return $this->db->$execFun($sql,$param);
	}
	
	public function selectByWhere($table="",$field="*",$where=array(),$whereStr = "",$order="",$group="")
	{
		return $this->selectByWhereExec("executeFetch",$table, $field,$where,$whereStr,$order,$group);
	}
	
	public function selectDataSetByWhere($table="",$field="*",$where=array(),$whereStr = "",$order="",$group="")
	{
		return $this->selectByWhereExec("executeDataSet",$table,$field,$where,$whereStr,$order,$group);
	}
	
	public function selectById($id,$where="",$field="*")
	{
		$param = array(
				array(":id",$id,PDO::PARAM_STR)
		);

		$isLongData = $this->isLongField($field);
		
		$field = $this->opDataSelectField($field);
		
		$sql = "select ".$field." from ".$this->getTabelName()." where ".$this->getPKID()."=:id";
	
		if($where)
		{
			$sql .= " and ".$where;
		}
		
		if($isLongData)
		{
			$params = array();
			$params["id"] = $id;
			return $this->db->selectLongDataSingle($sql,$params,$this->_longColumns);
		}
		return $this->db->executeFetch($sql,$param);
	}
	
	private function isLongField($field)
	{
		$isLongData = false;
		
		$temp = explode(",",$field);
		if($this->_longColumns)
		{
			if($field=="*")
			{
				$isLongData = true;
			}
			else{
				foreach($temp as $v)
				{
					if($v==$this->_longColumns)
					{
						$isLongData = true;
						break;
					}
				}
			}
		}
		return $isLongData;
	}
	
	public function updateSortNum($id,$num)
	{
		$param = array(
				array(":id",$id,PDO::PARAM_INT),
				array(":num",$num,PDO::PARAM_INT)
		);
	
		return $this->db->executeNonQuery("update ".$this->getTabelName()." set sort_num=:num where ".$this->getPKID()."=:id",$param);
	}
	
	public function getCountByWhere($where,$table="")
	{
		if($table=="")
		{
			$table = $this->getTabelName();
		}
		$where = $where?" where ".$where:"";
		return $this->db->executeScalar("select count(*) from ".$table.$where);
	}
	
	/**
	 * 分页方法
	 * @param int $pageIndex 页码
	 * @param int $pageSize 每页记录数
	 * @param string $pageFields 查询字段
	 * @param string $strWhere 查询条件,不需要带where关键字
	 * @param string $strOrder 排序,支持多个字段排序,如(id desc,name asc),不需要带order关键字
	 * @param string $strGroup 分组,不需要带group关键字
	 * @param int $recordCount 记录总数,通过该变量返回
	 * @return array 查询结果数据集
	 */
	public function selectPage($pageIndex, $pageSize, $pageFields="*", $strWhere="", $strOrder="", $strGroup="", &$recordCount=0, $table="")
	{
		$table = $table==""? $this->getTabelName():$table;
		if($strWhere)
		{
			$strWhere = " where (".$strWhere.")";
		}
	
		if($strGroup)
		{
			$strGroup = " group by ".$strGroup;
		}
	
		$sql = "";

		if($recordCount==0)
		{
			if($strGroup)
			{
				$sql = "select count(0) from (select count(0) from ".$table. $strWhere.$strGroup.") as a";
			}
			else
			{
				$sql = "select count(0) from ".$table. $strWhere;
			}
			
			$obj = $this->db->executeScalar($sql);
				
			if($obj===false)
			{
				$recordCount = 0;
			}
			else
			{
				$recordCount = $obj;
			}
		}

		if($recordCount ==0)
		{
			return array();
		}
	
		$pageFields = $this->opDataSelectField($pageFields);
		
		$operateor = "";
	
		if($strOrder)
		{
			$strOrder = " order by ".$strOrder;
		}
	
		if($pageIndex==0)
		{
			$pageIndex = 1;
		}
	
		$beginRow = ($pageIndex-1)*$pageSize;
		
	
		if($pageIndex==1)
		{
			$sql = "select A.*,ROWNUM RN from (select ".$pageFields." from ".$table.$strWhere.$strOrder.") A WHERE ROWNUM<=".$pageSize;
		}
		else
		{
			$endRow = $pageSize+$beginRow;
			$sql = "select * from (select A.*,ROWNUM RN from (select ".$pageFields." from ".$table.$strWhere.$strOrder.") A WHERE ROWNUM<=".$endRow.")  where RN>".$beginRow;
		}
		
		//var_dump($sql);exit;

		return $this->db->executeDataSet($sql);
	}
}

?>